package com.zzty.daoimpl;


import com.zzty.common.DBUtilPool;
import com.zzty.dao.TeamExamTransactionDao;
import com.zzty.entity.Group;
import com.zzty.entity.Groups;
import com.zzty.entity.TeamExamTransaction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;


public class TeamExamTransactionDaoImpl implements TeamExamTransactionDao {
    public String getUrl(Integer teamExamId,Integer groupId){
        String url=null;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn.prepareStatement("select answerUrl from teamExamTransaction where teamExamId=? and groupId=?");
            pst.setInt(1,teamExamId);
            pst.setInt(2,groupId);
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                url=rs.getString("answerUrl");
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return url;
    }
    public List getAnsewrUrlByTeamExamId(Integer teamExamId){
        List<String> aUrls=null;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn.prepareStatement("select * from teamExamTransaction where teamExamId=?");
            pst.setInt(1,teamExamId);
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                if(aUrls==null) {
                    aUrls=new ArrayList<String>();
                }
                    aUrls.add(rs.getString("answerUrl"));
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return aUrls;
    }
    public boolean setBeginTimeAndUserId(String startTime,Integer teamExamId,Integer groupId){
        boolean result=false;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn.prepareStatement("insert into teamExamTransaction(teamExamId,groupId,startTime) values(?,?,?)");
            pst.setInt(1,teamExamId);
            pst.setInt(2,groupId);
            pst.setString(3,startTime);
            Integer count=0;
            count=pst.executeUpdate();
            if(count>0){
                result=true;
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    public boolean updateTeamExamTransactionByUserId(TeamExamTransaction teamExamTransaction,Calendar cal){
        boolean result=false;
        Date startTime=new Date();
        String spendTime=null;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pstm=
                    cn.prepareStatement("select startTime from teamExamTransaction where groupId=?");
            pstm.setInt(1,teamExamTransaction.getGroupId());
            ResultSet rs=pstm.executeQuery();
            while (rs.next()){
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String sTime=sdf.format(rs.getTimestamp("startTime"));
                startTime=sdf.parse(sTime);
            }
            System.out.println("startTime="+startTime);
            System.out.println("cal="+cal.getTime());
            long diff = cal.getTime().getTime() - startTime.getTime();//这样得到的差值是微秒级别
            long days = diff / (1000 * 60 * 60 * 24);

            long hours = (diff-days*(1000 * 60 * 60 * 24))/(1000* 60 * 60);
            long minutes = (diff-days*(1000 * 60 * 60 * 24)-hours*(1000* 60 * 60))/(1000* 60);
            long seconds=(diff-days*(1000 * 60 * 60 * 24)-minutes*(1000*60))/(1000);
            spendTime=days+"天"+hours+"时"+minutes+"分"+seconds+"秒";
            System.out.println("spendTime="+spendTime);
            teamExamTransaction.setSpendTime(spendTime);
            Connection cn1=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn1.prepareStatement("update teamExamTransaction set answerUrl=?,spendTime=? where groupId=? and teamExamId=?");
            pst.setString(1,teamExamTransaction.getAnswerUrl());
            pst.setString(2,teamExamTransaction.getSpendTime());
            pst.setInt(3,teamExamTransaction.getGroupId());
            pst.setInt(4,teamExamTransaction.getTeamExamId());
            Integer count=0;
            count=pst.executeUpdate();
            if(count>0){
                result=true;
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    public List getGroupsByTeamExamId(Integer teamExamId){
        List<Groups> groups=null;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn.prepareStatement("select * from teamExamTransaction where teamExamId=?");
            pst.setInt(1,teamExamId);
            ResultSet rs=pst.executeQuery();
            while (rs.next()){
                    if(groups==null){
                        groups=new ArrayList<Groups>();
                    }
                    Groups group=new Groups();
                    group.setGroupId(rs.getInt("groupId"));
                    group.setScore(rs.getDouble("score"));
                    group.setSpendTime(rs.getString("spendTime"));
                    groups.add(group);
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return groups;
    }
    public boolean setScore(Integer teamExamId,Integer groupId,Integer score){
        boolean result=false;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn.prepareStatement("update  teamExamTransaction set score=? where teamExamId=? and groupId=? ");
            pst.setInt(1,score);
            pst.setInt(2,teamExamId);
            pst.setInt(3,groupId);
            Integer count=0;
            count=pst.executeUpdate();
            if(count>0){
                result=true;
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public List getTeamExamIdByGroupId(Integer groupId) {
        List<Integer> teamExamIds=null;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement ps=cn.prepareStatement("select teamExamId from teamExamTransaction where groupId=?");
            ps.setInt(1,groupId);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                if(teamExamIds==null){
                    teamExamIds=new ArrayList<Integer>();
                }
                Integer teamExamId=rs.getInt("teamExamId");
                teamExamIds.add(teamExamId);
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return teamExamIds;
    }

    @Override
    public Double getScoreByGroupIdByTeamExamId(Integer teamExamId, Integer groupId) {
        Double score=null;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement ps=cn.prepareStatement("select score from teamExamTransaction where teamExamId=? and groupId=?");
            ps.setInt(1,teamExamId);
            ps.setInt(2,groupId);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                score =rs.getDouble("score");
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return score;
    }

    @Override
    public boolean checkToday(Integer teamExamId, Integer groupId) {
        boolean result=false;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn.prepareStatement("select count(*) as count from teamExamTransaction where teamExamId=? and groupId=?");
            pst.setInt(1,teamExamId);
            pst.setInt(2,groupId);
            ResultSet rs=pst.executeQuery();
            while (rs.next()){
                Integer count=0;
                count=rs.getInt("count");
                if(count!=0){
                    result=true;
                }
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    public boolean checkTodaySubmit(Integer teamExamId, Integer groupId) {
        boolean result=false;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn.prepareStatement("select answerUrl from teamExamTransaction where teamExamId=? and groupId=?");
            pst.setInt(1,teamExamId);
            pst.setInt(2,groupId);
            ResultSet rs=pst.executeQuery();
            while (rs.next()){
                String answerUrl=null;
                answerUrl=rs.getString("answerUrl");
                if(answerUrl!=null){
                    result=true;
                }
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public String getBeginTime(Integer teamExamId, Integer groupId) {
        String sTime=null;
        try {
            Connection cn=DBUtilPool.getConnection();
            PreparedStatement pst=
                    cn.prepareStatement("select startTime from teamExamTransaction where teamExamId=? and groupId=?");
            pst.setInt(1,teamExamId);
            pst.setInt(2,groupId);
            ResultSet rs=pst.executeQuery();
            while (rs.next()){
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                sTime=sdf.format(rs.getTimestamp("startTime"));
            }
            DBUtilPool.releaseConnection(cn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sTime;
    }


}
